import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

public class Validator {
	private String name;
	private int col;
	private ArrayList<String> R;
	private ArrayList<String> Rs;
	private ArrayList<FuncDep> FDs;
	private ArrayList<ArrayList<String>> synonyms;
	private ArrayList<String> match_suggested;
	private ArrayList<String> match_synonyms;
	private ArrayList<ArrayList<String>> maxSets;
	private ArrayList<String[]> table;
	private ArrayList<String[]> sample;
	ArrayList<ArrayList<Integer>> combinations;

	// Constructor
	public Validator(String tableName) {
		this.R = new ArrayList<String>();
		this.name = tableName;
		this.table = new ArrayList<String[]>();
		this.combinations = new ArrayList<ArrayList<Integer>>();
		this.synonyms = new ArrayList<ArrayList<String>>();
		this.match_suggested = new ArrayList<String>();
		this.match_synonyms = new ArrayList<String>();
		readTable();
		init();
	}

	// Read information from table
	private void readTable() {
		Connection conn = null;
		String url = "jdbc:mysql://localhost/";
		String dbName = "FYP";
		String driver = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "";

		try {
			Class.forName(driver);

			// Open connection
			conn = DriverManager
					.getConnection(url + dbName, userName, password);
			System.out.println("Connected to the database");
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT * FROM " + this.name);

			// Get the number of columns
			ResultSetMetaData rsmd = rs.getMetaData();
			this.col = rsmd.getColumnCount();

			// Getting column names and synonyms
			for (int i = 0; i < this.col; i++) {
				this.R.add(rsmd.getColumnName(i + 1));
				this.synonyms.add(new ArrayList<String>());
			}

			// Process results one row at a time
			while (rs.next()) {
				String[] row = new String[this.col];

				for (int i = 0; i < this.col; i++) {
					row[i] = rs.getString(i + 1);
					if (row[i] == null) {
						row[i] = "null";
					} else {
						if (!this.synonyms.get(i).contains(row[i])) {
							this.synonyms.get(i).add(row[i]);
						}
					}
				}

				this.table.add(row);
			}

			// Close connection
			conn.close();
			System.out.println("Disconnected from database");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// Initialize data information
	private void init() {
		setup();
		setupSampleDatabase();
	}

	// Validation
	public String validate() {

		// Sampled database
		System.out.println("Sampled database generated:");
		for (int i = 0; i < this.sample.size(); i++) {
			for (int j = 0; j < this.col; j++) {
				System.out.print(this.sample.get(i)[j] + " ");
			}
			System.out.println("\n");
		}

		System.out.println("VERIFICATION RESULT");
		// Setting the combinations
		if (this.table.size() != this.sample.size()) {
			return "NOT PERFECT IN SIZE";
		} else {
			ArrayList<Integer> curr = new ArrayList<Integer>();
			getCombination(this.sample.size(), curr);
		}

		boolean perfect = false;

		// Checking process
		for (int i = 0; i < this.combinations.size(); i++) {
			if (verifyDatabase(this.combinations.get(i))) {
				return "PERFECT";
			}
		}

		if (!perfect) {
			return "NOT PERFECT";
		}

		return "UNKNOWN";
	}

	// Method for verification of 2 databases
	private boolean verifyDatabase(ArrayList<Integer> order) {
		ArrayList<String> map_sample = new ArrayList<String>();
		ArrayList<String> map_table = new ArrayList<String>();

		// Checking every row
		for (int i = 0; i < order.size(); i++) {
			for (int j = 0; j < this.col; j++) {
				String samp = this.sample.get(i)[j];
				String tabl = this.table.get(order.get(i))[j];
				int index = map_sample.indexOf(samp);

				// If this entry is not known yet
				if (index == -1) {
					if (map_table.contains(tabl)) {
						return false;
					} else {
						map_sample.add(samp);
						map_table.add(tabl);
					}
				}

				// If it is known
				else {
					if (!tabl.equals(map_table.get(index))) {
						return false;
					}
				}
			}
		}

		return true;
	}

	// Compute the combinations of all the possible arrangements
	private void getCombination(int size, ArrayList<Integer> past) {
		if (past.size() == size) {
			this.combinations.add(past);
		} else {
			for (int i = 0; i < size; i++) {
				if (!past.contains(i)) {
					ArrayList<Integer> curr = new ArrayList<Integer>();
					for (int j = 0; j < past.size(); j++) {
						curr.add(past.get(j));
					}
					curr.add(i);
					getCombination(size, curr);
				}
			}
		}
	}

	// Setting the sample database as a main criteria to check.
	private void setupSampleDatabase() {
		this.sample = new ArrayList<String[]>();
		SetManipulator sm = new SetManipulator();
		ArrayList<String> nullAdded = new ArrayList<String>();
		int index = 1;

		for (int i = 0; i < this.maxSets.size(); i++) {
			ArrayList<String> X = maxSets.get(i);
			ArrayList<String> Z = new ArrayList<String>();

			for (int k = 0; k < maxSets.size(); k++) {
				if (sm.belongs(X, maxSets.get(k))) {
					Z.add(this.R.get(k));
				}
			}

			ArrayList<String> XZRs = sm.union3(X, Z, this.Rs);
			ArrayList<String> Rs_X = sm.subtract(this.Rs, X);
			ArrayList<String> ZRs_X = sm.union2(Z, Rs_X);

			// Checking duplicates
			int dup = 0;

			String[] row1 = new String[this.col];
			String[] row2 = new String[this.col];

			// Going row by pair of rows
			for (int j = 0; j < this.col; j++) {

				// First row in pair
				if (XZRs.contains(this.R.get(j))) {
					row1[j] = this.R.get(j) + "_" + index;

					int pos = this.match_suggested.indexOf(row1[j]);

					if (pos != -1) {
						row1[j] = this.match_synonyms.get(pos);
					} else {
						if (this.synonyms.get(j).size() > 0) {
							this.match_suggested.add(row1[j]);
							row1[j] = this.synonyms.get(j).get(0);
							this.match_synonyms.add(row1[j]);
							this.synonyms.get(j).remove(0);
						}
					}
				} else {
					row1[j] = "null";
					if (!nullAdded.contains(this.R.get(j))) {
						nullAdded.add(this.R.get(j));
					}
				}

				// Second row in pair
				if (X.contains(this.R.get(j))) {
					row2[j] = this.R.get(j) + "_" + index;

					int pos = this.match_suggested.indexOf(row2[j]);

					if (pos != -1) {
						row2[j] = this.match_synonyms.get(pos);
					} else {
						if (this.synonyms.get(j).size() > 0) {
							this.match_suggested.add(row2[j]);
							row2[j] = this.synonyms.get(j).get(0);
							this.match_synonyms.add(row2[j]);
							this.synonyms.get(j).remove(0);
						}
					}
				} else {
					if (ZRs_X.contains(this.R.get(j))) {
						row2[j] = this.R.get(j) + "_" + (index + 1);

						int pos = this.match_suggested.indexOf(row2[j]);

						if (pos != -1) {
							row2[j] = this.match_synonyms.get(pos);
						} else {
							if (this.synonyms.get(j).size() > 0) {
								this.match_suggested.add(row2[j]);
								row2[j] = this.synonyms.get(j).get(0);
								this.match_synonyms.add(row2[j]);
								this.synonyms.get(j).remove(0);
							}
						}

						dup++;
					} else {
						row2[j] = "null";
						if (!nullAdded.contains(this.R.get(j))) {
							nullAdded.add(this.R.get(j));
						}
					}
				}
			}

			if (dup == this.col) {
				this.sample.add(row1);
				index += 1;
			} else {
				this.sample.add(row1);
				this.sample.add(row2);
				index += 2;
			}
		}

		// Making sure there is NULL items
		ArrayList<String> nullables = new ArrayList<String>();
		nullables = sm.subtract(this.R, this.Rs);
		ArrayList<String> leftover = new ArrayList<String>();
		leftover = sm.subtract(nullables, nullAdded);

		if (leftover.size() > 0) {
			String[] row = new String[this.col];
			for (int i = 0; i < this.col; i++) {
				if (leftover.contains(this.R.get(i))) {
					row[i] = null;
				} else {
					row[i] = this.R.get(i);
				}
			}
			this.sample.add(row);
		}
	}

	// Set up necessary information
	private void setup() {
		DBManipulator db = new DBManipulator();
		this.Rs = db.getNullFreeSubSchema(this.table, this.col, this.R);
		this.FDs = db.getFDs(this.table, this.col, this.R);

		SetManipulator sm = new SetManipulator();
		this.maxSets = sm.computeMaxSets(this.R, this.Rs, this.FDs);
	}

	public ArrayList<String> getAttributes() {
		return this.R;
	}

	public ArrayList<String> getNullfreeSubschema() {
		return this.Rs;
	}

	public ArrayList<FuncDep> getFDs() {
		return this.FDs;
	}

	public ArrayList<String[]> getGeneratedSampleDB() {
		return this.sample;
	}
}
